package com.apexsoft.assets.base.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.apexsoft.assets.base.model.Result;
import com.apexsoft.assets.base.exception.BizException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

import javax.sql.DataSource;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * description:
 *
 * @author guojieming1
 * @date 2021/8/20 15:34
 */
@Slf4j
public class SimpleJdbcCallUtil {

    public static Map<String,Object> generalExecuteProcedure(String procName, Map<String, Object> paramToValueMap, DataSource dataSource,String dbType){
        if(StringUtils.isBlank(procName)){
            throw new BizException("错误,接口信息为空，请检查通用列表配置");
        }
        SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(dataSource).withProcedureName(procName);
        //拼接SQL获取过程参数
        List<Map<String, Object>> paramInfoList = simpleJdbcCall.getJdbcTemplate().queryForList(buildProcQueryInPara(procName,dbType));
        Map<String, Object> paramMap = new LinkedHashMap<>();
        paramInfoList.forEach(paramInfo->{
            String name = (String) paramInfo.get("NAME");
            String lowerCaseName = name.toLowerCase();
            String upperCaseName = name.toUpperCase();
            if(paramToValueMap.containsKey(lowerCaseName)||paramToValueMap.containsKey(upperCaseName)){
                paramMap.put(name,paramToValueMap.get(lowerCaseName)!=null?paramToValueMap.get(lowerCaseName):paramToValueMap.get(upperCaseName));
            }else {
                paramMap.put(name,null);
            }
        });

        SqlParameterSource sqlParameterSource = new MapSqlParameterSource().addValues(paramMap);
        log.info("Begin procedure call: {} with parameters: [{}]",
                procName,
                paramMap);
        long begin = System.currentTimeMillis();
        Map<String, Object> outMap = simpleJdbcCall.execute(sqlParameterSource);
        long end = System.currentTimeMillis();
        long duration = end-begin;
        log.info("Finish calling procedure: {} within {}ms.", procName, duration);
        return outMap;
    }

    public static Map<String,Object> executeProcedure(String procName, Map<String, Object> paramToValueMap, DataSource dataSource,String dbType){
        if(StringUtils.isBlank(procName)){
            throw new BizException("错误,接口信息为空，请检查通用列表配置");
        }
        //获取userId和userName
        Integer uid = (Integer) paramToValueMap.get("uid");
        String userName = (String) paramToValueMap.get("userName");
        SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(dataSource).withProcedureName(procName);
        //拼接SQL获取过程参数
        List<Map<String, Object>> paramInfoList = simpleJdbcCall.getJdbcTemplate().queryForList(buildProcQueryInPara(procName,dbType));
        Map<String, Object> paramMap = new LinkedHashMap<>();
        //自定义query参数
        String queryKey = (String)paramToValueMap.get("queryKey");
        if(StringUtils.isBlank(queryKey)){
            /*
             * query为空则直接对应存储过程参数名传参
             */
            paramInfoList.forEach(paramInfo->{
                String name = (String) paramInfo.get("NAME");
                String lowerCaseName = name.toLowerCase();
                String upperCaseName = name.toUpperCase();
                if(paramToValueMap.containsKey(lowerCaseName)||paramToValueMap.containsKey(upperCaseName)){
                    paramMap.put(name,paramToValueMap.get(lowerCaseName)!=null?paramToValueMap.get(lowerCaseName):paramToValueMap.get(upperCaseName));
                }else {
                    if("I_USERID".equalsIgnoreCase(name)){
                        paramMap.put(name,uid);
                    }else if("I_USERNAME".equalsIgnoreCase(name)){
                        paramMap.put(name,userName);
                    }else {
                        paramMap.put(name,null);
                    }
                }
            });
        }else {
            /*
             * query不为空则严格按照过程参数顺序进行参数填充
             */
            String[] queryArray = queryKey.split(",");
            if(CollectionUtils.isNotEmpty(paramInfoList)){
                for(int i=0;i<paramInfoList.size();i++){
                    String name = (String) paramInfoList.get(i).get("NAME");
                    String key = queryArray[i].trim();
                    if("$uid".equals(key)){
                        paramMap.put(name,uid);
                    }else if("$userName".equals(key)){
                        paramMap.put(name,userName);
                    }else {
                        paramMap.put(name,paramToValueMap.get(key));
                    }
                }
            }else{
                throw new BizException("Stored Procedure not found: "+procName);
            }
        }

        SqlParameterSource sqlParameterSource = new MapSqlParameterSource().addValues(paramMap);
        log.info("Begin procedure call: {} with parameters: [{}]",
                procName,
                paramMap);
        long begin = System.currentTimeMillis();
        Map<String, Object> outMap = simpleJdbcCall.execute(sqlParameterSource);
        long end = System.currentTimeMillis();
        long duration = end-begin;
        log.info("Finish calling procedure: {} within {}ms.", procName, duration);
        return outMap;
    }

    public static Map<String, Object> callProcedure(String procName, Map<String, Object> paramToValueMap, DataSource dataSource){
        SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(dataSource);
        String dbType = "oracle";
        if(dataSource instanceof DruidDataSource){
            dbType = ((DruidDataSource) dataSource).getDbType();
        }
        String sql = buildProcInfoQueryString(procName,dbType);
        List<Map<String, Object>> paramInfoList = simpleJdbcCall.getJdbcTemplate().queryForList(sql);
//        boolean hasResultSet = false;
        Map<String, Object> paramMap = new LinkedHashMap<>();
        if(CollectionUtils.isNotEmpty(paramInfoList)){
            for (Map<String, Object> param : paramInfoList){
                paramMap.put((String) param.get("NAME"), null);
//                if("O_RESULT".equals(param.get("NAME"))){
//                    hasResultSet = true;
//                }
            }
        }else{
            throw new BizException("Stored Procedure not found: "+procName);
        }

        paramMap.putAll(paramToValueMap);

        simpleJdbcCall.withProcedureName(procName);
//        if(hasResultSet){
//            simpleJdbcCall.returningResultSet("O_CODE", BeanPropertyRowMapper.newInstance(T.class));
//        }
        SqlParameterSource sqlParameterSource = new MapSqlParameterSource().addValues(paramMap);
        log.info("Begin procedure call: {} with parameters: [{}]",
                procName,
                paramMap);
        long begin = System.currentTimeMillis();
        Map<String, Object> outMap = simpleJdbcCall.execute(sqlParameterSource);
        long end = System.currentTimeMillis();
        long duration = end-begin;
        log.info("Finish calling procedure: {} within {}ms.", procName, duration);
//        AResponse<T> result = new AResponse<>();
//        result.setCode((Integer.parseInt(String.valueOf(outMap.get("O_CODE")))));
//        result.setNote(String.valueOf(outMap.get("O_NOTE")));
//        if(hasResultSet){
//            Object o_result = outMap.get("O_RESULT");
//            if(o_result instanceof List){
//                result.setRecords((List<T>) o_result);
//            }else if(o_result instanceof T){
//                result.setData(o_result);
//            }
//        }
        return outMap;
    }

    public static List<Map<String, Object>> callProcedureForList(String procName, Map paramToValueMap, DataSource dataSource){
        return (List<Map<String, Object>>) callProcedure(procName, paramToValueMap, dataSource).get("O_RESULT");
    }

    public static Result callProcedureForResult(String procName, Map<String, Object> paramToValueMap, DataSource dataSource){
        Result result = new Result();
        Map<String, Object> map = callProcedure(procName, paramToValueMap, dataSource);
        result.setCode((Integer.parseInt(String.valueOf(map.get("O_CODE")))));
        result.setNote(String.valueOf(map.get("O_NOTE")));
        return result;
    }

    private static String buildProcInfoQueryString(String procName,String dbType){
        if(StringUtils.endsWithIgnoreCase("oracle",dbType)){
            return "SELECT A.ARGUMENT_NAME NAME " +
                    "  FROM SYS.USER_ARGUMENTS A " +
                    "  WHERE  " +
                    "  A.OBJECT_NAME='" + procName + "' " +
                    "  ORDER BY A.POSITION";
        }else if (StringUtils.endsWithIgnoreCase("dm",dbType)){
            return "SELECT T2.NAME NAME " +
                    " FROM SYSOBJECTS T1,SYSCOLUMNS T2" +
                    " WHERE T1.TYPE$ = 'SCHOBJ'" +
                    " AND T1.SUBTYPE$ = 'PROC'" +
                    " AND T1.ID = T2.ID" +
                    " AND T1.NAME = '" + procName + "' " +
                    " ORDER BY T1.ID,T2.COLID ASC";
        }
        return "";
    }

    private static String buildProcQueryInPara(String procName,String dbType){
        if(StringUtils.endsWithIgnoreCase("oracle",dbType)){
            return "SELECT A.ARGUMENT_NAME NAME " +
                    "  FROM SYS.USER_ARGUMENTS A " +
                    "  WHERE  " +
                    "  (A.IN_OUT='IN' OR A.IN_OUT='IN/OUT') AND" +
                    "  A.OBJECT_NAME='" + procName + "' " +
                    "  ORDER BY A.POSITION";
        }else if (StringUtils.endsWithIgnoreCase("dm",dbType)){
            return "SELECT T2.NAME NAME " +
            " FROM SYSOBJECTS T1,SYSCOLUMNS T2" +
            " WHERE T1.TYPE$ = 'SCHOBJ'" +
            " AND T1.SUBTYPE$ = 'PROC'" +
            " AND T1.ID = T2.ID" +
            " AND T1.NAME = '" + procName + "' " +
            " AND T2.INFO1 IN(0,2)" +
            " ORDER BY T1.ID,T2.COLID ASC";
        }
        return "";
    }

    public static Map<String, Object> callProcedureByList(String procName, List valueList, DataSource dataSource){
        SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(dataSource);
        String dbType = "oracle";
        if(dataSource instanceof DruidDataSource){
            dbType = ((DruidDataSource) dataSource).getDbType();
        }
        String sql = buildProcInfoQueryString(procName,dbType);
        List<Map<String, Object>> paramInfoList = simpleJdbcCall.getJdbcTemplate().queryForList(sql);
//        boolean hasResultSet = false;
        Map<String, Object> paramMap = new LinkedHashMap<>();
        if(CollectionUtils.isNotEmpty(paramInfoList)){
            if(CollectionUtils.isEmpty(valueList) || paramInfoList.size()!=valueList.size()){
                throw new BizException("Parameter list mismatch with value list: "+procName);
            }
            for (int i = 0; i < paramInfoList.size(); i++) {
                paramMap.put((String) paramInfoList.get(i).get("NAME"), valueList.get(i));
            }
//                if("O_RESULT".equals(param.get("NAME"))){
//                    hasResultSet = true;
//                }
        }else{
            throw new BizException("Stored Procedure not found: "+procName);
        }
        simpleJdbcCall.withProcedureName(procName);
//        if(hasResultSet){
//            simpleJdbcCall.returningResultSet("O_CODE", BeanPropertyRowMapper.newInstance(T.class));
//        }
        SqlParameterSource sqlParameterSource = new MapSqlParameterSource().addValues(paramMap);
        log.info("Begin procedure call: {} with parameters: [{}]",
                procName,
                paramMap);
        long begin = System.currentTimeMillis();
        Map<String, Object> outMap = simpleJdbcCall.execute(sqlParameterSource);
        long end = System.currentTimeMillis();
        long duration = end-begin;
        log.info("Finish calling procedure: {} within {}ms.", procName, duration);
//        AResponse<T> result = new AResponse<>();
//        result.setCode((Integer.parseInt(String.valueOf(outMap.get("O_CODE")))));
//        result.setNote(String.valueOf(outMap.get("O_NOTE")));
//        if(hasResultSet){
//            Object o_result = outMap.get("O_RESULT");
//            if(o_result instanceof List){
//                result.setRecords((List<T>) o_result);
//            }else if(o_result instanceof T){
//                result.setData(o_result);
//            }
//        }
        return outMap;
    }
}
